unit viewDPT;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, ZAbstractTable, ZDataset, DB, ZAbstractRODataset, ZAbstractDataset,
  ZConnection, StdCtrls, Grids, Setting;

type
  TPemilih = record
     ID:String;
     NIK:String;
     NKK:String;
     NAMA:String;
     TEMPAT:String;
     TANGGAL:String;
     IBU:String;
     AGAMA:String;
     KAWIN:String;
     KELAMIN:String;
     KELAINAN:String;
     ALAMAT:String;
     KOTA:String;
     KEC:String;
     DESA:String;
     RW:String;
     RT:String;
     DUSUN:String;
     KET:String;
  end;
  TTPemilih = array of TPemilih;
  TPenyimpan = record
     ID:String;
     NAMA:String;
  end;
  TTPenyimpan = array of TPenyimpan;
  TDpt = record
     KODE:String;
     NAMA:String;
  end;
  TTDpt = array of TDpt;
  TTabel = record
     ID:String;
     TABEL:String;
     COUNT:Integer;
  end;
  TTTabel = array of TTabel;
  TGanda = record
     ID1:String;
     ID2:String;
     KET:Integer;
  end;
  TTGanda = array of TGanda;
  TviewDPT_Form = class(TForm)
    btnCari: TButton;
    btnCarinama: TButton;
    Button1: TButton;
    CheckBox1: TCheckBox;
    CheckBox2: TCheckBox;
    CheckBox3: TCheckBox;
    CheckBox4: TCheckBox;
    ComboBox1: TComboBox;
    ComboBox2: TComboBox;
    ComboBox3: TComboBox;
    GroupBox1: TGroupBox;
    lblHal: TLabel;
    btnNext: TButton;
    btnPrev: TButton;
    StringGrid1: TStringGrid;
    txtHal: TEdit;
    Label1: TLabel;
    Label2: TLabel;
    Label3: TLabel;
    lblProses: TLabel;
    txtNama: TEdit;
    ZConnection1: TZConnection;
    ZQuery1: TZQuery;
    ZQuery2: TZQuery;
    ZTable1: TZTable;
    ZTable2: TZTable;
  private
    procedure AutoSizeCol(Grid: TStringGrid; Column: integer);
    procedure btnCariClick(Sender: TObject);
    procedure btnCarinamaClick(Sender: TObject);
    procedure btnHalClick(Sender: TObject);
    procedure btnNextClick(Sender: TObject);
    procedure btnPrevClick(Sender: TObject);
    procedure ComboBox1Change(Sender: TObject);
    procedure ComboBox2Change(Sender: TObject);
    procedure ComboBox3Change(Sender: TObject);
    procedure FetchDB;
    procedure FetchDBNama;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure FormCreate(Sender: TObject);
    procedure GetData(Hal: Integer);
    procedure GetDataNama(Hal: Integer);
    function getDesa(kode: String): String;
    function getIdsimpan(tabel: String): String;
    function getIduser(id: String): String;
    function getKabkot(kode: String): String;
    function getKec(kode: String): String;
    procedure getPage;
    function getSimpan(id: String): String;
    function getTabel(id: String): String;
    procedure sizeMove(var msg: TWMSize);
    procedure StringGrid1Click(Sender: TObject);
    procedure TampilHalaman(Hal:Integer = 1);
    procedure txtHalKeyDown(Sender: TObject; var Key: Word; Shift: TShiftState);
    procedure txtNamaChange(Sender: TObject);
    { Private declarations }
  public
    { Public declarations }
  end;

var
  viewDPT_Form: TviewDPT_Form;
  cnama:Boolean;
  sKabkot:String;
  sKec:String;
  sDesa:String;
  cRow:Integer; //currentrow
  dRow:Integer; //data row
  cHal:Integer; //current halaman
  jHal:Integer; //jumlah halaman
  bHal:Integer; //batas per halaman
  bPemilih: TTPemilih;
  bPenyimpan:TTPenyimpan;
  bDesa:TTDpt;
  bKec:TTDpt;
  bKabkot:TTDpt;
  bTabel:TTTabel;
  bGanda:TTGanda;
  bIdganda:array of String;
  bGridganda:TTPenyimpan;
  fData:Boolean;
  ptable,ptable2:String;
  pid,pid2,tid,tid2:String;

implementation

{$R *.dfm}

procedure TviewDPT_Form.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  Action := caFree;
  Finalize(bKabkot);
  Finalize(bKec);
  Finalize(bDesa);
  Finalize(bPemilih);
  Finalize(bPenyimpan);
end;

procedure TviewDPT_Form.btnCariClick(Sender: TObject);
begin
  cnama:=false;
  FetchDB;
end;

procedure TviewDPT_Form.btnCarinamaClick(Sender: TObject);
begin
  cnama:=true;
  FetchDBNama;
end;

Procedure TviewDPT_Form.sizeMove (var msg: TWMSize);
begin
 inherited;
 if (Self.ComboBox1.ItemIndex>-1) then begin
   if (msg.SizeType = SIZE_MAXIMIZED) OR (msg.SizeType = SIZE_RESTORED) then begin
     GroupBox1.Width:=Width-30;
     StringGrid1.Width:=Width-40;
   end;
 end;
end;

procedure TviewDPT_Form.btnHalClick(Sender: TObject);
begin
  getPage;
end;

procedure TviewDPT_Form.btnNextClick(Sender: TObject);
begin
  TampilHalaman(cHal+1);
  txtHal.Text:=IntToStr(cHal);
end;

procedure TviewDPT_Form.btnPrevClick(Sender: TObject);
begin
  TampilHalaman(cHal-1);
  txtHal.Text:=IntToStr(cHal);
end;

procedure TviewDPT_Form.ComboBox1Change(Sender: TObject);
var
  pilihan:String;
begin
  if (ComboBox1.ItemIndex > 0) then
  begin
    pilihan := ComboBox1.Items[ComboBox1.ItemIndex];
    with ZTable1 do
      begin
        Active := False;
        TableName := 'kabkot';
        Filter := 'TAMPIL = '+QuotedStr(pilihan);
        Filtered := True;
        Active := True;
        First;
        sKabkot := FieldByName('KODE').AsString;
        //cb2
        Active := False;
        TableName := 'kecamatan';
        Filter := 'KODE_KABKOT = '+QuotedStr(sKabkot);
        Filtered := True;
        Active := True;
        First;
      end;
    ComboBox2.Clear;
    ComboBox3.Clear;
    ComboBox2.Items.Add('SEMUA KECAMATAN');
    while (not ZTable1.Eof) do
      begin
        ComboBox2.Items.Add(ZTable1.FieldByName('TAMPIL').AsString);
        ZTable1.Next;
      end;
  end
    else
  begin
    sKabkot:='';
    ComboBox2.Clear;
    ComboBox3.Clear;
  end;
  sKec:='';
  sDesa:='';
  btnCari.Enabled:=True;
end;

procedure TviewDPT_Form.ComboBox2Change(Sender: TObject);
var
  pilihan:String;
begin
  if (ComboBox2.ItemIndex > 0) then
  begin
    pilihan := ComboBox2.Items[ComboBox2.ItemIndex];
    ZTable1.Active := False;
    ZTable1.TableName := 'kecamatan';
    ZTable1.Filter := 'TAMPIL = '+QuotedStr(pilihan);
    ZTable1.Filtered := True;
    ZTable1.Active := True;
    ZTable1.First;
    sKec := ZTable1.FieldByName('KODE_KECAMATAN').AsString;
    //cb3
    ZTable1.Active := False;
    ZTable1.TableName := 'desa';
    ZTable1.Filter := 'KODE_KECAMATAN = '+QuotedStr(sKec);
    ZTable1.Filtered := True;
    ZTable1.Active := True;
    ZTable1.First;
    ComboBox3.Clear;
    ComboBox3.Items.Add('SEMUA DESA');
    while (not ZTable1.Eof) do
      begin
        ComboBox3.Items.Add(ZTable1.FieldByName('TAMPIL').AsString);
        ZTable1.Next;
      end;
  end
    else
  begin
    sKec:='';
    ComboBox3.Clear;
  end;
  sDesa:='';
end;

procedure TviewDPT_Form.ComboBox3Change(Sender: TObject);
var
  desa:String;
begin
  if (ComboBox3.ItemIndex > 0) then
  begin
    desa := ComboBox3.Items[ComboBox3.ItemIndex];
    //
    ZTable1.Active := False;
    ZTable1.TableName := 'desa';
    ZTable1.Filter := 'TAMPIL = '+QuotedStr(desa)+' AND KODE_KABKOT = '+QuotedStr(sKabkot);
    ZTable1.Filtered := True;
    ZTable1.Active := True;
    ZTable1.First;
    sDesa := ZTable1.FieldByName('KODE_DESA').AsString;
  end
    else
  begin
    sDesa:='';
  end;
end;

procedure TviewDPT_Form.FetchDB();
var
  kode:String;
  query:String;
  kKota:String;
  kKec:String;
  kDesa:String;
  tblName:String;
  i:Integer;
  count:Integer;
  gdata:Boolean;
  que:String;
begin
  lblProses.Caption:='Harap tunggu sedang proses';
  lblHal.Caption:='-';
  gdata:=False;
  for i := 1 to StringGrid1.RowCount - 1
  do StringGrid1.Rows[i].Clear;
  StringGrid1.RowCount:=2;
  if (sDesa<>'') then
    begin
      kode:=sDesa;
    end
  else if (sKec<>'') then
    begin
      kode:=sKec;
    end
  else if (sKabkot<>'') then
    begin
      kode:=sKabkot;
    end
  else
    begin
      kode:='';
    end;
  if (kode='') then
    query:='1'
  else
    query:='NAMA LIKE "dpt_'+kode+'%"';
  ZQuery1.Active := False;
  query:= 'SELECT * FROM penyimpan_dpt WHERE '+query;
  ZQuery1.SQL.Text := query;
  ZQuery1.Active := True;
  ZQuery1.First;  //dapat tabel yang di simpan
  SetLength(bTabel,ZQuery1.RecordCount);
  i:=0;
  count:=0;
  while (not ZQuery1.Eof) do
    begin
      gdata:=True;
      ZQuery2.Active := False;
      tblName:=ZQuery1.FieldByName('NAMA').AsString;
      ZQuery2.SQL.Clear;
      que:='SELECT COUNT(*) FROM '+tblname;
      if (CheckBox1.Checked) or (CheckBox2.Checked) or (CheckBox3.Checked) or (CheckBox4.Checked) then begin
        que:=que+' WHERE ';
        if (CheckBox1.Checked) then que:=que+'STATUS2='+QuotedStr('1')+' OR ';
        if (CheckBox2.Checked) then que:=que+'STATUS2='+QuotedStr('2')+' OR ';
        if (CheckBox3.Checked) then que:=que+'STATUS2='+QuotedStr('4')+' OR ';
        if (CheckBox4.Checked) then que:=que+'STATUS2='+QuotedStr('3')+' OR ';
        que:=Copy(que,0,(Length(que)-4));
      end
        else
      begin
        que:=que+' WHERE STATUS2="0" OR STATUS2="4"';
      end;
//      showmessage(que);
      ZQuery2.SQL.Text:=que;
      ZQuery2.Active := True;
      ZQuery2.First;  //tabel
      count:=count+ZQuery2.Fields[0].AsInteger;
      //ShowMessage(IntToStr(count));
      if (ZQuery2.Fields[0].AsInteger>0) then begin
        bTabel[i].ID:=ZQuery1.FieldByName('ID').AsString;
        bTabel[i].TABEL:=ZQuery1.FieldByName('NAMA').AsString;
        bTabel[i].COUNT:=count;
        Inc(i);
      end
        else
      SetLength(bTabel,length(btabel)-1);
      ZQuery1.Next;
    end;
    lblProses.Caption:='';
    jHal:=count div bHal;
    if ((count mod bHal)>0) then jHal:=jHal+1;
    if (gdata and (count>0)) then TampilHalaman(1);
end;

procedure TviewDPT_Form.FetchDBNama();
var
  kode:String;
  query:String;
  kKota:String;
  kKec:String;
  kDesa:String;
  tblName:String;
  i:Integer;
  count:Integer;
  gdata:Boolean;
  que:String;
begin
  lblProses.Caption:='Harap tunggu sedang proses';
  lblHal.Caption:='-';
  gdata:=False;
  for i := 1 to StringGrid1.RowCount - 1
  do StringGrid1.Rows[i].Clear;
  StringGrid1.RowCount:=2;
  if (sDesa<>'') then
    begin
      kode:=sDesa;
    end
  else if (sKec<>'') then
    begin
      kode:=sKec;
    end
  else if (sKabkot<>'') then
    begin
      kode:=sKabkot;
    end
  else
    begin
      kode:='';
    end;
  if (kode='') then
    query:='1'
  else
    query:='NAMA LIKE "dpt_'+kode+'%"';
  ZQuery1.Active := False;
  query:= 'SELECT * FROM penyimpan_dpt WHERE '+query;
  ZQuery1.SQL.Text := query;
  ZQuery1.Active := True;
  ZQuery1.First;  //dapat tabel yang di simpan
  SetLength(bTabel,ZQuery1.RecordCount);
  i:=0;
  count:=0;
  while (not ZQuery1.Eof) do
    begin
      gdata:=True;
      ZQuery2.Active := False;
      tblName:=ZQuery1.FieldByName('NAMA').AsString;
      ZQuery2.SQL.Clear;
      que:='SELECT COUNT(*) FROM '+tblname;
      if (CheckBox1.Checked) or (CheckBox2.Checked) or (CheckBox3.Checked) or (CheckBox4.Checked) then begin
        que:=que+' WHERE NAMA LIKE "%'+txtNama.Text+'%" AND (';
        if (CheckBox1.Checked) then que:=que+'STATUS2='+QuotedStr('1')+' OR ';
        if (CheckBox2.Checked) then que:=que+'STATUS2='+QuotedStr('2')+' OR ';
        if (CheckBox3.Checked) then que:=que+'STATUS2='+QuotedStr('4')+' OR ';
        if (CheckBox4.Checked) then que:=que+'STATUS2='+QuotedStr('3')+' OR ';
        que:=Copy(que,0,(Length(que)-4));
        que:=que+')';
      end
        else
      begin
        que:=que+' WHERE NAMA LIKE "%'+txtNama.Text+'%" AND (STATUS2="0" OR STATUS2="4")';
      end;
//      showmessage(que);
      ZQuery2.SQL.Text:=que;
      ZQuery2.Active := True;
      ZQuery2.First;  //tabel
      count:=count+ZQuery2.Fields[0].AsInteger;
      //ShowMessage(IntToStr(count));
      if (ZQuery2.Fields[0].AsInteger>0) then begin
        bTabel[i].ID:=ZQuery1.FieldByName('ID').AsString;
        bTabel[i].TABEL:=ZQuery1.FieldByName('NAMA').AsString;
        bTabel[i].COUNT:=count;
        Inc(i);
      end
        else
      SetLength(bTabel,length(btabel)-1);
      ZQuery1.Next;
    end;
    lblProses.Caption:='';
    jHal:=count div bHal;
    if ((count mod bHal)>0) then jHal:=jHal+1;
//    ShowMessage(IntToStr(count));
    if (gdata and (count>0)) then TampilHalaman(1);
end;

procedure TviewDPT_Form.GetData(Hal:Integer);
var
  query:String;
  kKota:String;
  kKec:String;
  kDesa:String;
  tabel:TTabel;
  tblName,idsimpan,iduser,idganda:String;
  i:Integer;
  vRow,ujung,awal:Integer;
begin
  dRow:=1;
  vRow:=1;
  //cek
  ujung:=Hal*bHal;
  i:=0;
  if (jHal>1) then begin
    for I := 0 to (Length(bTabel)) - 1 do begin
      if (ujung<=bTabel[i].COUNT) then
        break;
    end;
    if (i>0) then begin
      ujung:=ujung-btabel[i-1].COUNT;
      if (ujung<bHal) then begin
        Dec(i);
        awal:=bTabel[i].COUNT-(bHal-ujung);
      end else
        awal:=ujung-bHal;
    end
    else begin
      awal:=ujung-bHal;
    end;
  end
    else
  begin
    awal:=0;
    ujung:=bhal;
  end;
  //showmessage(IntToStr(length(btabel)));
  tblName:=btabel[i].TABEL;
  idsimpan:=btabel[i].ID;
  ZQuery1.Active := False;
  query:= 'SELECT * FROM '+tblName;
  if (CheckBox1.Checked) or (CheckBox2.Checked) or (CheckBox3.Checked) or (CheckBox4.Checked) then begin
    query:=query+' WHERE ';
    if (CheckBox1.Checked) then query:=query+'STATUS2='+QuotedStr('1')+' OR ';
    if (CheckBox2.Checked) then query:=query+'STATUS2='+QuotedStr('2')+' OR ';
    if (CheckBox3.Checked) then query:=query+'STATUS2='+QuotedStr('4')+' OR ';
    if (CheckBox4.Checked) then query:=query+'STATUS2='+QuotedStr('3')+' OR ';
    query:=Copy(query,0,(Length(query)-4));
  end
    else
  begin
    query:=query+' WHERE STATUS2="0" OR STATUS2="4"';
  end;
  query:=query+' ORDER BY NKK ASC LIMIT '+IntToStr(awal)+','+IntToStr(bhal);
  ZQuery1.SQL.Text := query;
  ZQuery1.Active := True;
  ZQuery1.First;  //tabel simpan
  //
  kKota:=Copy(tblName,5,2);
  kKota:=getKabkot(kKota);
  kKec:=Copy(tblName,5,5);
  kKec:=getKec(kKec);
  kDesa:=Copy(tblName,5,8);
  kDesa:=getDesa(kDesa);
  //
  dRow:=dRow+ZQuery1.RecordCount;
  SetLength(bPemilih,dRow);
  while (not ZQuery1.Eof) do begin
    bPemilih[vRow].ID :=tblName+ZQuery1.Fields[0].AsString;
    bPemilih[vRow].NIK:=ZQuery1.Fields[1].AsString;
    bPemilih[vRow].NKK:=ZQuery1.Fields[2].AsString;
    bPemilih[vRow].NAMA:=ZQuery1.Fields[3].AsString;
    bPemilih[vRow].TEMPAT:=ZQuery1.Fields[4].AsString;
    bPemilih[vRow].TANGGAL:=ZQuery1.Fields[5].AsString;
    bPemilih[vRow].IBU:=ZQuery1.Fields[6].AsString;
    bPemilih[vRow].AGAMA:=ZQuery1.Fields[7].AsString;
    bPemilih[vRow].KAWIN:=ZQuery1.Fields[8].AsString;
    bPemilih[vRow].KELAMIN:=ZQuery1.Fields[9].AsString;
    bPemilih[vRow].KELAINAN:=ZQuery1.Fields[10].AsString;
    bPemilih[vRow].ALAMAT:=ZQuery1.Fields[11].AsString;
    bPemilih[vRow].KOTA:=kKota;
    bPemilih[vRow].KEC:=kKec;
    bPemilih[vRow].DESA:=kDesa;
    bPemilih[vRow].RW:=ZQuery1.Fields[12].AsString;
    bPemilih[vRow].RT:=ZQuery1.Fields[13].AsString;
    bPemilih[vRow].DUSUN:=ZQuery1.Fields[14].AsString;
    bPemilih[vRow].KET:=ZQuery1.Fields[15].AsString;
    Inc(vrow);
    ZQuery1.Next;
  end;
  if ((ujung<bhal) and (jhal>1)) then begin
    inc(i);
    tblName:=btabel[i].TABEL;
    idsimpan:=btabel[i].ID;
    ZQuery1.Active := False;
    query:= 'SELECT * FROM '+tblName;
    if (CheckBox1.Checked) or (CheckBox2.Checked) or (CheckBox3.Checked) or (CheckBox4.Checked) then begin
      query:=query+' WHERE ';
      if (CheckBox1.Checked) then query:=query+'STATUS2='+QuotedStr('1')+' OR ';
      if (CheckBox2.Checked) then query:=query+'STATUS2='+QuotedStr('2')+' OR ';
      if (CheckBox3.Checked) then query:=query+'STATUS2='+QuotedStr('4')+' OR ';
      if (CheckBox4.Checked) then query:=query+'STATUS2='+QuotedStr('3')+' OR ';
      query:=Copy(query,0,(Length(query)-4));
    end
      else
    begin
      query:=query+' WHERE STATUS2="0" OR STATUS2="4"';
    end;
    query:=query+' ORDER BY NKK ASC LIMIT 0,'+IntToStr(ujung);
    //ShowMessage(query);
    ZQuery1.SQL.Text := query;
    ZQuery1.Active := True;
    ZQuery1.First;  //tabel simpan
    //
  kKota:=Copy(tblName,5,2);
  kKota:=getKabkot(kKota);
  kKec:=Copy(tblName,5,5);
  kKec:=getKec(kKec);
  kDesa:=Copy(tblName,5,8);
  kDesa:=getDesa(kDesa);
    //
    dRow:=dRow+ZQuery1.RecordCount;
    SetLength(bPemilih,dRow);
    while (not ZQuery1.Eof) do begin
      bPemilih[vRow].ID :=tblName+ZQuery1.Fields[0].AsString;
      bPemilih[vRow].NIK:=ZQuery1.Fields[1].AsString;
      bPemilih[vRow].NKK:=ZQuery1.Fields[2].AsString;
      bPemilih[vRow].NAMA:=ZQuery1.Fields[3].AsString;
      bPemilih[vRow].TEMPAT:=ZQuery1.Fields[4].AsString;
      bPemilih[vRow].TANGGAL:=ZQuery1.Fields[5].AsString;
      bPemilih[vRow].IBU:=ZQuery1.Fields[6].AsString;
      bPemilih[vRow].AGAMA:=ZQuery1.Fields[7].AsString;
      bPemilih[vRow].KAWIN:=ZQuery1.Fields[8].AsString;
      bPemilih[vRow].KELAMIN:=ZQuery1.Fields[9].AsString;
      bPemilih[vRow].KELAINAN:=ZQuery1.Fields[10].AsString;
      bPemilih[vRow].ALAMAT:=ZQuery1.Fields[11].AsString;
      bPemilih[vRow].KOTA:=kKota;
      bPemilih[vRow].KEC:=kKec;
      bPemilih[vRow].DESA:=kDesa;
      bPemilih[vRow].RW:=ZQuery1.Fields[12].AsString;
      bPemilih[vRow].RT:=ZQuery1.Fields[13].AsString;
      bPemilih[vRow].DUSUN:=ZQuery1.Fields[14].AsString;
      bPemilih[vRow].KET:=ZQuery1.Fields[15].AsString;
      Inc(vrow);
      ZQuery1.Next;
    end;
  end;
end;

procedure TviewDPT_Form.TampilHalaman(Hal:Integer = 1);
var
  iter,i:Integer;
  tabel,iduser,tanda,query:String;
begin
  txtHal.Enabled:=True;
  lblHal.Caption := IntToStr(Hal)+' of '+IntToStr(jHal);
  cHal:=Hal;
  if (cHal=1) then
    btnPrev.Enabled:=False
  else
    btnPrev.Enabled:=True;
  if (cHal=jHal) then
    btnNext.Enabled:=False
  else
    btnNext.Enabled:=True;
  //get to bpemilih
  if cnama=false then
    GetData(Hal)
  else
    GetDataNama(Hal);
  cRow:=1;
  SetLength(bGanda,0);
  while (cRow<=bHal) and (crow<=High(bpemilih)) do
   begin
    StringGrid1.RowCount:=cRow+1;
    tabel:=getTabel(bPemilih[cRow].ID);
    tabel:=getIdsimpan(tabel);
    iduser:=getIduser(bPemilih[cRow].ID);
    tanda:='';
    //------------------------------------
    StringGrid1.Cells[0,cRow]:=IntToStr((cHal-1)*bHal+cRow)+tanda;
    StringGrid1.Cells[1,cRow]:=bPemilih[cRow].NIK;
    StringGrid1.Cells[2,cRow]:=bPemilih[cRow].NKK;
    StringGrid1.Cells[3,cRow]:=bPemilih[cRow].NAMA;
    StringGrid1.Cells[4,cRow]:=bPemilih[cRow].TEMPAT;
    StringGrid1.Cells[5,cRow]:=bPemilih[cRow].TANGGAL;
    StringGrid1.Cells[6,cRow]:=bPemilih[cRow].IBU;
    StringGrid1.Cells[7,cRow]:=bPemilih[cRow].AGAMA;
    StringGrid1.Cells[8,cRow]:=bPemilih[cRow].KAWIN;
    StringGrid1.Cells[9,cRow]:=bPemilih[cRow].KELAMIN;
    StringGrid1.Cells[10,cRow]:=bPemilih[cRow].KELAINAN;
    StringGrid1.Cells[11,cRow]:=bPemilih[cRow].ALAMAT;
    StringGrid1.Cells[12,cRow]:=bPemilih[cRow].KOTA;
    StringGrid1.Cells[13,cRow]:=bPemilih[cRow].KEC;
    StringGrid1.Cells[14,cRow]:=bPemilih[cRow].DESA;
    StringGrid1.Cells[15,cRow]:=bPemilih[cRow].RW;
    StringGrid1.Cells[16,cRow]:=bPemilih[cRow].RT;
    StringGrid1.Cells[17,cRow]:=bPemilih[cRow].DUSUN;
    StringGrid1.Cells[18,cRow]:=bPemilih[cRow].KET;
    Inc(crow);
   end;
   //autosize
   for i := 0 to StringGrid1.ColCount - 1 do
    AutoSizeCol(StringGrid1, i);
end;

procedure TviewDPT_Form.GetDataNama(Hal:Integer);
var
  query:String;
  kKota:String;
  kKec:String;
  kDesa:String;
  tabel:TTabel;
  tblName,idsimpan,iduser,idganda:String;
  i:Integer;
  vRow,ujung,awal:Integer;
begin
  dRow:=1;
  vRow:=1;
  //cek
  ujung:=Hal*bHal;
  i:=0;
  if (jHal>1) then begin
    for I := 0 to (Length(bTabel)) - 1 do begin
      if (ujung<=bTabel[i].COUNT) then
        break;
    end;
    if (i>0) then begin
      ujung:=ujung-btabel[i-1].COUNT;
      if (ujung<bHal) then begin
        Dec(i);
        awal:=bTabel[i].COUNT-(bHal-ujung);
      end else
        awal:=ujung-bHal;
    end
    else begin
      awal:=ujung-bHal;
    end;
  end
    else
  begin
    awal:=0;
    ujung:=bhal;
  end;
  //showmessage(IntToStr(length(btabel)));
  tblName:=btabel[i].TABEL;
  idsimpan:=btabel[i].ID;
  ZQuery1.Active := False;
  query:= 'SELECT * FROM '+tblName;
  if (CheckBox1.Checked) or (CheckBox2.Checked) or (CheckBox3.Checked) or (CheckBox4.Checked) then begin
    query:=query+' WHERE NAMA LIKE "%'+txtNama.Text+'%" AND (';
    if (CheckBox1.Checked) then query:=query+'STATUS2='+QuotedStr('1')+' OR ';
    if (CheckBox2.Checked) then query:=query+'STATUS2='+QuotedStr('2')+' OR ';
    if (CheckBox3.Checked) then query:=query+'STATUS2='+QuotedStr('4')+' OR ';
    if (CheckBox4.Checked) then query:=query+'STATUS2='+QuotedStr('3')+' OR ';
    query:=Copy(query,0,(Length(query)-4));
    query:=query+')';
  end
    else
  begin
    query:=query+' WHERE NAMA LIKE "%'+txtNama.Text+'%" AND (STATUS2="0" OR STATUS2="4")';
  end;
  query:=query+' ORDER BY NKK ASC LIMIT '+IntToStr(awal)+','+IntToStr(bhal);
//  ShowMessage(query);
  ZQuery1.SQL.Text := query;
  ZQuery1.Active := True;
  ZQuery1.First;  //tabel simpan
  //
  kKota:=Copy(tblName,5,2);
  kKota:=getKabkot(kKota);
  kKec:=Copy(tblName,5,5);
  kKec:=getKec(kKec);
  kDesa:=Copy(tblName,5,8);
  kDesa:=getDesa(kDesa);
  //
  dRow:=dRow+ZQuery1.RecordCount;
  SetLength(bPemilih,dRow);
  while (not ZQuery1.Eof) do begin
    bPemilih[vRow].ID :=tblName+ZQuery1.Fields[0].AsString;
    bPemilih[vRow].NIK:=ZQuery1.Fields[1].AsString;
    bPemilih[vRow].NKK:=ZQuery1.Fields[2].AsString;
    bPemilih[vRow].NAMA:=ZQuery1.Fields[3].AsString;
    bPemilih[vRow].TEMPAT:=ZQuery1.Fields[4].AsString;
    bPemilih[vRow].TANGGAL:=ZQuery1.Fields[5].AsString;
    bPemilih[vRow].IBU:=ZQuery1.Fields[6].AsString;
    bPemilih[vRow].AGAMA:=ZQuery1.Fields[7].AsString;
    bPemilih[vRow].KAWIN:=ZQuery1.Fields[8].AsString;
    bPemilih[vRow].KELAMIN:=ZQuery1.Fields[9].AsString;
    bPemilih[vRow].KELAINAN:=ZQuery1.Fields[10].AsString;
    bPemilih[vRow].ALAMAT:=ZQuery1.Fields[11].AsString;
    bPemilih[vRow].KOTA:=kKota;
    bPemilih[vRow].KEC:=kKec;
    bPemilih[vRow].DESA:=kDesa;
    bPemilih[vRow].RW:=ZQuery1.Fields[12].AsString;
    bPemilih[vRow].RT:=ZQuery1.Fields[13].AsString;
    bPemilih[vRow].DUSUN:=ZQuery1.Fields[14].AsString;
    bPemilih[vRow].KET:=ZQuery1.Fields[15].AsString;
    Inc(vrow);
    ZQuery1.Next;
  end;
  if ((ujung<bhal) and (jhal>1)) then begin
    inc(i);
    tblName:=btabel[i].TABEL;
    idsimpan:=btabel[i].ID;
    ZQuery1.Active := False;
    query:= 'SELECT * FROM '+tblName;
    if (CheckBox1.Checked) or (CheckBox2.Checked) or (CheckBox3.Checked) or (CheckBox4.Checked) then begin
      query:=query+' WHERE NAMA LIKE "%'+txtNama.Text+'%" AND (';
      if (CheckBox1.Checked) then query:=query+'STATUS2='+QuotedStr('1')+' OR ';
      if (CheckBox2.Checked) then query:=query+'STATUS2='+QuotedStr('2')+' OR ';
      if (CheckBox3.Checked) then query:=query+'STATUS2='+QuotedStr('4')+' OR ';
      if (CheckBox4.Checked) then query:=query+'STATUS2='+QuotedStr('3')+' OR ';
      query:=Copy(query,0,(Length(query)-4));
      query:=query+')';
    end
      else
    begin
      query:=query+' WHERE NAMA LIKE "%'+txtNama.Text+'%" AND (STATUS2="0" OR STATUS2="4")';
    end;
    query:=query+' ORDER BY NKK ASC LIMIT 0,'+IntToStr(ujung);
//    ShowMessage(query);
    ZQuery1.SQL.Text := query;
    ZQuery1.Active := True;
    ZQuery1.First;  //tabel simpan
    //
    kKota:=Copy(tblName,5,2);
    kKota:=getKabkot(kKota);
    kKec:=Copy(tblName,5,5);
    kKec:=getKec(kKec);
    kDesa:=Copy(tblName,5,8);
    kDesa:=getDesa(kDesa);
    //
    dRow:=dRow+ZQuery1.RecordCount;
    SetLength(bPemilih,dRow);
    while (not ZQuery1.Eof) do begin
      bPemilih[vRow].ID :=tblName+ZQuery1.Fields[0].AsString;
      bPemilih[vRow].NIK:=ZQuery1.Fields[1].AsString;
      bPemilih[vRow].NKK:=ZQuery1.Fields[2].AsString;
      bPemilih[vRow].NAMA:=ZQuery1.Fields[3].AsString;
      bPemilih[vRow].TEMPAT:=ZQuery1.Fields[4].AsString;
      bPemilih[vRow].TANGGAL:=ZQuery1.Fields[5].AsString;
      bPemilih[vRow].IBU:=ZQuery1.Fields[6].AsString;
      bPemilih[vRow].AGAMA:=ZQuery1.Fields[7].AsString;
      bPemilih[vRow].KAWIN:=ZQuery1.Fields[8].AsString;
      bPemilih[vRow].KELAMIN:=ZQuery1.Fields[9].AsString;
      bPemilih[vRow].KELAINAN:=ZQuery1.Fields[10].AsString;
      bPemilih[vRow].ALAMAT:=ZQuery1.Fields[11].AsString;
      bPemilih[vRow].KOTA:=kKota;
      bPemilih[vRow].KEC:=kKec;
      bPemilih[vRow].DESA:=kDesa;
      bPemilih[vRow].RW:=ZQuery1.Fields[12].AsString;
      bPemilih[vRow].RT:=ZQuery1.Fields[13].AsString;
      bPemilih[vRow].DUSUN:=ZQuery1.Fields[14].AsString;
      bPemilih[vRow].KET:=ZQuery1.Fields[15].AsString;
      Inc(vrow);
      ZQuery1.Next;
    end;
  end;
end;

function TviewDPT_Form.getTabel(id:String):String;
begin
  Result:=Copy(id,1,12);
end;

function TviewDPT_Form.getIduser(id:String):String;
begin
  Result:=Copy(id,13,(Length(id)-12));
end;

function TviewDPT_Form.getDesa(kode:String):String;
var
  I:integer;
begin
  for I := 0 to Length(bDesa) - 1 do begin
    if (bDesa[i].KODE=kode) then
      break;
  end;
    Result:=bDesa[i].NAMA;
end;

function TviewDPT_Form.getKec(kode:String):String;
var
  I:integer;
begin
  for I := 0 to Length(bKec) - 1 do begin
    if (bKec[i].KODE=kode) then
      break;
  end;
    Result:=bKec[i].NAMA;
end;

function TviewDPT_Form.getKabkot(kode:String):String;
var
  I:integer;
begin
  for I := 0 to Length(bKabkot) - 1 do begin
    if (bKabkot[i].KODE=kode) then
      break;
  end;
    Result:=bKabkot[i].NAMA;
end;

function TviewDPT_Form.getIdsimpan(tabel:String):String;
var
  I:integer;
  res:String;
begin
  res:='';
  for I := 0 to Length(bPenyimpan) - 1 do begin
    if (bPenyimpan[i].NAMA=tabel) then begin
      res:=bPenyimpan[i].ID;
      break;
    end;
  end;
    Result:=res;
end;

function TviewDPT_Form.getSimpan(id:String):String;
var
  I:integer;
  res:String;
begin
  res:='';
  for I := 0 to Length(bPenyimpan) - 1 do begin
    if (bPenyimpan[i].ID=id) then begin
      res:=bPenyimpan[i].NAMA;
      break;
    end;
  end;
    Result:=res;
end;

procedure TviewDPT_Form.txtHalKeyDown(Sender: TObject; var Key: Word;
  Shift: TShiftState);
begin
  if key = VK_RETURN then
    getPage;
end;

procedure TviewDPT_Form.txtNamaChange(Sender: TObject);
begin
  if txtNama.Text<>'' then
    btnCarinama.Enabled:=true
  else
    btnCarinama.Enabled:=false;
end;

procedure TviewDPT_Form.getPage();
begin
    if ((txtHal.Text='') or (StrToInt(txtHal.Text)>jHal) or (StrToInt(txtHal.Text)<1)) then
      ShowMessage('Isi dahulu dengan halaman yang sesuai')
    else if (jHal=0) then
      ShowMessage('Tidak ada halaman yang dapat ditampilkan')
    else
      begin
        TampilHalaman(StrToInt(txtHal.Text));
      end;
end;

procedure TviewDPT_Form.StringGrid1Click(Sender: TObject);
var
  pos,tots,i:Integer;
  tid:String;
begin
  if (StringGrid1.Row>0) then begin
      pos:=StringGrid1.Row;
      //getID
      ptable:=Copy(bPemilih[pos].ID,1,16);
      tots:=Length(bPemilih[pos].ID)-16;
      pid:=Copy(bPemilih[pos].ID,17,tots);
      tid:=getIdsimpan(ptable);
  end;
end;

procedure TviewDPT_Form.AutoSizeCol(Grid: TStringGrid; Column: integer);
var
  i, W, WMax: integer;
begin
  WMax := 0;
  for i := 0 to (Grid.RowCount - 1) do begin
    W := Grid.Canvas.TextWidth(Grid.Cells[Column, i]);
    if W > WMax then
      WMax := W;
  end;
  Grid.ColWidths[Column] := WMax + 5;
end;

procedure TviewDPT_Form.FormCreate(Sender: TObject);
var
  I: Integer;
  setting:TSetting_Form;
begin
  setting:=TSetting_Form.Create;
  ZConnection1.HostName:=setting.host;
  ZConnection1.Protocol:=setting.protocol;
  ZConnection1.Database:=setting.database;
  ZConnection1.User:=setting.user;
  ZConnection1.Password:=setting.password;
  ZConnection1.Connected:=true;
  ztable1.Connection:=ZConnection1;
  ZQuery1.Connection:=ZConnection1;
  ztable2.Connection:=ZConnection1;
  ZQuery2.Connection:=ZConnection1;
  ZConnection1.LoginPrompt:=False;
  ZConnection1.Connected := True;
  //
  sKabkot:='';
  sKec:='';
  sDesa:='';
  lblProses.Caption:='';
  cRow:=2;  //wadah row
  dRow:=1;  //menyimpan banyaknya data
  bHal:=40;  //pemilih per halaman
  lblHal.Caption:= '-';
  btnCari.Enabled:=False;
  txtHal.Enabled:=False;
  btnPrev.Enabled:=False;
  btnNext.Enabled:=False;
  btnCarinama.Enabled:=False;
  //grid
  StringGrid1.Cells[0,0]:='NO';
  StringGrid1.Cells[1,0]:='NIK';
  StringGrid1.Cells[2,0]:='NKK';
  StringGrid1.Cells[3,0]:='Nama';
  StringGrid1.Cells[4,0]:='Tempat Lahir';
  StringGrid1.Cells[5,0]:='Tanggal Lahir';
  StringGrid1.Cells[6,0]:='Nama Ibu';
  StringGrid1.Cells[7,0]:='Agama';
  StringGrid1.Cells[8,0]:='Perkawinan';
  StringGrid1.Cells[9,0]:='Jenis Kelamin';
  StringGrid1.Cells[10,0]:='Kelainan';
  StringGrid1.Cells[11,0]:='Alamat';
  StringGrid1.Cells[12,0]:='Kota';
  StringGrid1.Cells[13,0]:='Kecamatan';
  StringGrid1.Cells[14,0]:='Desa';
  StringGrid1.Cells[15,0]:='RW';
  StringGrid1.Cells[16,0]:='RT';
  StringGrid1.Cells[17,0]:='Dusun';
  StringGrid1.Cells[18,0]:='Ket';
  StringGrid1.RowCount:=cRow;
  //bpenyimpan
  with ZTable1 do begin
    Active:=False;
    TableName:='penyimpan';
    Active:=True;
    SetLength(bPenyimpan,RecordCount);
    I:=0;
    while (not Eof) do begin
      bPenyimpan[I].ID:=FieldByName('ID').AsString;
      bPenyimpan[I].NAMA:=FieldByName('NAMA').AsString;
      Inc(I);
      Next;
    end;
  end;
  //desa
  with ZTable1 do begin
    Active:=False;
    TableName:='desa';
    Active:=True;
    SetLength(bDesa,RecordCount);
    I:=0;
    while (not Eof) do begin
      bDesa[I].KODE:=FieldByName('KODE_DESA').AsString;
      bDesa[I].NAMA:=FieldByName('TAMPIL').AsString;
      Inc(I);
      Next;
    end;
  end;
  //kec
  with ZTable1 do begin
    Active:=False;
    TableName:='kecamatan';
    Active:=True;
    SetLength(bKec,RecordCount);
    I:=0;
    while (not Eof) do begin
      bKec[I].KODE:=FieldByName('KODE_KECAMATAN').AsString;
      bKec[I].NAMA:=FieldByName('TAMPIL').AsString;
      Inc(I);
      Next;
    end;
  end;
  //kota
  with ZTable1 do begin
    Active:=False;
    TableName:='kabkot';
    Active:=True;
    SetLength(bKabkot,RecordCount);
    I:=0;
    while (not Eof) do begin
      bKabkot[I].KODE:=FieldByName('KODE').AsString;
      bKabkot[I].NAMA:=FieldByName('TAMPIL').AsString;
      Inc(I);
      Next;
    end;
    ComboBox1.Clear;
    ComboBox1.Items.Add('SEMUA KAB/KOTA');
    for I := 0 to Length(bKabkot) - 1 do
      begin
        ComboBox1.Items.Add(bKabkot[i].NAMA);
      end;
    ComboBox1.ItemIndex:=0;
  end;
end;

end.
